package com.css.fxfzdpzs.modules.xczh.repository.impl;

import com.css.fxfzdpzs.modules.xczh.entity.first.*;
import com.css.fxfzdpzs.modules.xczh.entity.second.*;
import com.css.fxfzdpzs.modules.xczh.externalEntity.EarEveManageInfoVo;
import com.css.fxfzdpzs.modules.xczh.repository.XczhRepository;
import com.css.fxfzdpzs.modules.xczh.vo.*;
import com.css.fxfzdpzs.util.PlatformObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.List;

@Repository
public class XczhRepositoryImpl implements XczhRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Override
    public GradeDisasterNum getDisasterNum() {
        String sql = "SELECT * FROM xczh_5grade_disaster_num";
        List<GradeDisasterNum> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GradeDisasterNum.class));
        if (PlatformObjectUtils.isEmpty(list)) {
            return null;
        }
        return list.get(0);
    }

    /**
     * 灾损数量前五省份
     *
     * @return
     */
    @Override
    public List<DisasterNumVo> getDamageNum() {
//        String sql = "SELECT province,COUNT(*) disasterDamageNum  from xczh_disaster_damage GROUP BY province ORDER BY COUNT(*) DESC LIMIT 5 OFFSET 0";
//        List<DisasterNumVo> disasterNumVos = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterNumVo.class));
//        return disasterNumVos;
        String sql = "SELECT count(1) disasterDamageNum,province FROM xczh_disaster_damage_data GROUP BY province order by disasterDamageNum desc limit 5";

        //String sql = "SELECT * from xczh_disaster_count ORDER BY disaster_damage_num DESC LIMIT 5 OFFSET 0";
        List<DisasterNumVo> disasterNumVos = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterNumVo.class));
        return disasterNumVos;
    }

    @Override
    public List<DisasterDamageData> getLossProportion() {
        String sql = "SELECT * FROM xczh_disaster_damage_data order by occurrence_time desc limit 5";
        List<DisasterDamageData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterDamageData.class));
        return list;
    }

    @Override
    public List<DisasterAssessData> getUnitCount() {

//        String sql = "SELECT unit disasterEstimateUnit,COUNT(*) disasterEstimateNum from xczh_disaster_damage GROUP BY unit ORDER BY COUNT(*) DESC LIMIT 5 OFFSET 0";
        String sql = "SELECT assess_unit,assess_number FROM xczh_disaster_assess_data ORDER BY assess_unit desc limit 2";
        List<DisasterAssessData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterAssessData.class));
        return list;
    }

    @Override
    public List<DisasterValueVo> getDisasterValue() {
//        String sql = "SELECT province ,sum(loss) disasterValue from xczh_disaster_damage GROUP BY province ORDER BY sum(loss) DESC LIMIT 5 OFFSET 0";
        //String sql = "SELECT * from xczh_disaster_count ORDER BY disaster_value DESC LIMIT 5 OFFSET 0";
        String sql = "SELECT province,COALESCE(ROUND(SUM(loss_value) / 10000, 2), 0) AS disasterValue" +
                " FROM xczh_disaster_damage_data GROUP BY province order by disasterValue desc limit 5";

        List<DisasterValueVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterValueVo.class));
        return list;
    }

    @Override
    public List<DisasterVisitsCount> getVisitNum(String system) {
        String sql = "SELECT * FROM xczh_disaster_visits_count where system ='" + system + "'";
        List<DisasterVisitsCount> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterVisitsCount.class));
        return list;
    }

    @Override
    public List<DisasterDamage> getSituation() {
        String sql = "SELECT * FROM xczh_disaster_damage";
        List<DisasterDamage> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterDamage.class));
        return list;
    }

    @Override
    public DisasterVisitsCount getVisitNumByPro(String province) {
        String sql = "SELECT * FROM xczh_disaster_visits_count where system ='2' and province ='" + province + "'";
        List<DisasterVisitsCount> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterVisitsCount.class));
        if (list.size()==0)return null;
        return list.get(0);
    }

    @Override
    public List<XcDisasterImage> getDisasterImage(String iaId,String intensity) {
        String sql = "select * from xczh_disaster_image where  intensity='" +intensity +"' order by sort";
        List<XcDisasterImage> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(XcDisasterImage.class));
        return list;
    }

    @Override
    public XcDataNum getXcProportion(String iaId) {
        try {
            String sql = "select * from xczh_data_num where ia_id='"+iaId+"'";
            XcDataNum xcDataNum = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(XcDataNum.class));
            return xcDataNum;
        }catch (EmptyResultDataAccessException e){
            return new XcDataNum();
        }
    }

    @Override
    public XcMapEntity getXcMapEntity() {
        try {
            String sql = "select * from xczh_home_map ";
            XcMapEntity entity = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(XcMapEntity.class));
            return entity;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public List<XcMapEntityVO> getCountySituation() {
        String sql = "select *,ST_AsText(geom) geom  from xczh_county_situation ";
        List<XcMapEntityVO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(XcMapEntityVO.class));
        return list;
    }

    @Override
    public List<XcCountySituation> getCountyDisasterByCountyCode(String countyCode) {
        String sql = "select county,occurrence_time originTime,magnitude,loss_value from xczh_disaster_damage_data " +
                "where county_code= ? order by occurrence_time desc";
        List<XcCountySituation> list = jdbcTemplate.query(sql, new Object[]{countyCode},new BeanPropertyRowMapper<>(XcCountySituation.class));
        return list;
    }

    @Override
    public List<XcResultDate> getResultDates() {
        String sql = "select *,ST_AsText(geom) geom  from xczh_result_date order by intensity ";
        List<XcResultDate> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(XcResultDate.class));
        return list;
    }

    @Override
    public XcSecondMapEntity getSecondMapDate() {
        try {
            String sql = "select * from xczh_home_map ";
            XcSecondMapEntity entity = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(XcSecondMapEntity.class));
            return entity;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public List<DisasterDamageVo> getAllDisasterDamageData() {
        String sql = "SELECT county_code, county, longitude, latitude,magnitude,SUM(loss_value) loss_value " +
                "                FROM xczh_disaster_damage_data  " +
                "                WHERE occurrence_time > '1989-01-01'  " +
                "                GROUP BY county_code, county, longitude, latitude, loss_value, magnitude;";
        List<DisasterDamageVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DisasterDamageVo.class));
        return list;
    }

    @Override
    public String getGeomByLonAndLat(BigDecimal longitude, BigDecimal latitude) {
        String sql = "SELECT ST_AsText(geom) FROM district_boundary_county " +
                "WHERE ST_Within(st_geomfromtext('POINT("+longitude+" "+latitude+")',4490),geom) limit 1;";
        String geom = jdbcTemplate.queryForObject(sql, String.class);
        return geom;
    }

    @Override
    public DisasterDamageVo getMaxLossAndMinLoss() {
        String sql = "select max(loss_value) maxLoss,min(loss_value) minLoss from xczh_disaster_damage_data";
        DisasterDamageVo damageVo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DisasterDamageVo.class));
        return damageVo;
    }

    @Override
    public Integer getCount() {
        String sql = "select count(1) from xczh_disaster_damage_data";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }

    @Override
    public XcCountySituation getDisasterInfoByCondition(BigDecimal longitude, BigDecimal latitude) {
        String sql = "select county,occurrence_time originTime,magnitude,loss_value from xczh_disaster_damage_data " +
                "where longitude = ? and latitude = ?  order by occurrence_time desc";
        XcCountySituation xcCountySituation = jdbcTemplate.queryForObject(sql, new Object[]{longitude,latitude}, new BeanPropertyRowMapper<>(XcCountySituation.class));
        return xcCountySituation;
    }

    @Override
    public void delXcDataNum() {
        jdbcTemplate.update("delete from xczh_data_num");
    }

    @Override
    public void saveXcDataNum(List<XcDataNum> xcDataNums) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(xcDataNums.toArray());
        String sql = "INSERT INTO  xczh_data_num  " +
                "(id,survey_proportion,satellite_proportion,uav_proportion,instrument_proportion,ia_id,url) " +
                "values" +
                "(:id,:surveyProportion,:satelliteProportion,:uavProportion,:instrumentProportion,:iaId,:url)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void delResultDate() {
        jdbcTemplate.update("delete from xczh_result_date");
    }

    @Override
    public void saveXcResultDate(List<XcResultDate> intensityMaps) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(intensityMaps.toArray());
        String sql = "INSERT INTO  xczh_result_date  " +
                "(id,intensity,geom,xsem_id) " +
                "values" +
                "(:id,:intensity,ST_GeomFromText(:geom,4490),:xsemId)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public List<XcResultDate> getResultDatesByXsemId(String xsemId) {
        String sql = "SELECT *,ST_AsText(geom) geom FROM xczh_result_date " +
                "WHERE xsem_id = '"+xsemId+"'";
        List<XcResultDate> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(XcResultDate.class));
        return list;
    }

    @Override
    public void delDisasterImage() {
        jdbcTemplate.update("delete from xczh_disaster_image");
    }

    @Override
    public void saveDisasterImage(List<XcDisasterImage> xcDisasterImages) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(xcDisasterImages.toArray());
        String sql = "INSERT INTO  xczh_disaster_image  " +
                "(id,sort,url,intensity,ia_id) " +
                "values" +
                "(:id,:sort,:url,:intensity,:iaId)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

}
